﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using INTEX.DataBase.Tools;
using ReflectionStudio.Core.Events;
namespace WPFExcelReport
{    
    public partial class JLTWB14RData : BaseFunction
    {
        ApplicationBase app
        {
            get
            {
                return ApplicationBase.Instance;
            }
        }
        INTEXDBC INTEXDBC
        {
            get
            {
                return app.INTEXDBC;
            }
        }
        /*public INTEXBL INTEXBL
        {
            get
            {
                return app.INTEXBL;
            }
        }

        SETINTEXDB INTEXDB = new SETINTEXDB();
         */
        public JLTWB14RData()
            : base()
        {
        }
         public JLTWB14RData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetWB14IN(DataRow dwTABEL)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "WB14R";
            string ART = dwTABEL["ART"].ToString().Trim();
            string FROMDATE = dwTABEL["FROMDATE"].ToString().Trim();
            string ENDDATE = dwTABEL["ENDDATE"].ToString().Trim();
            try
            {
                orlReadCommand = INTEXDBC.OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT TO_CHAR(A.WBEWBUCHDAT) AS DAT,TRIM(A.WBEWART)||'-'||TRIM(A.WBEWFARBE) AS ARTICLE, "
                    + " SUM(A.WBEWMTR*0.001) AS MTR,SUM((CASE WHEN A.WBEWMTR<0 THEN -1 ELSE 1 END)) AS PCE "
                    + " FROM WBBEW A "
                    + " WHERE A.WBEWWAKZ = 'R' AND A.WBEWBS IN ('21','01') "
                    + " AND A.WBEWLO = '01' AND A.WBEWLB = 'PB' "
                    + " AND A.WBEWBUCHDAT >= '" + FROMDATE + "' AND A.WBEWBUCHDAT <= '" + ENDDATE + "' "
                    + " AND TRIM(A.WBEWART)||'-'||TRIM(A.WBEWFARBE) LIKE '" + ART + "%' "
                    + " GROUP BY A.WBEWART,A.WBEWFARBE,A.WBEWBUCHDAT "
                    + " ORDER BY 1,2 "
                    ;

                Result = INTEXDBC.Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    //byte[] b;
                    //string value;
                    //foreach (DataRow dw in Result.Tables[TableName].Rows)
                    //{
                    //    b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["REMARK"].ToString().Trim());
                    //    value = Encoding.GetEncoding("gb2312").GetString(b);
                    //    dw["REMARK"] = value;
                       
                    //}
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        public DataSet GetWB14OUT(DataRow dwTABEL)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "WB14R";
            string ART = dwTABEL["ART"].ToString().Trim();
            string FROMDATE = dwTABEL["FROMDATE"].ToString().Trim();
            string ENDDATE = dwTABEL["ENDDATE"].ToString().Trim();
            try
            {
                orlReadCommand = INTEXDBC.OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT TO_CHAR(A.WBEWBUCHDAT) AS DAT,TRIM(A.WBEWART)||'-'||TRIM(A.WBEWFARBE) AS ARTICLE, "
                    + " SUM(A.WBEWMTR*0.001) AS MTR,SUM((CASE WHEN A.WBEWMTR<0 THEN -1 ELSE 1 END)) AS PCE "
                    + " FROM WBBEW A "
                    + " WHERE A.WBEWWAKZ = 'R' AND A.WBEWBS = '20' "
                    + " AND A.WBEWLO = '01' AND A.WBEWLB = 'PB' "
                    + " AND A.WBEWBUCHDAT >= '" + FROMDATE + "' AND A.WBEWBUCHDAT <= '" + ENDDATE + "' "
                    + " AND TRIM(A.WBEWART)||'-'||TRIM(A.WBEWFARBE) LIKE '" + ART + "%' "
                    + " GROUP BY A.WBEWART,A.WBEWFARBE,A.WBEWBUCHDAT "
                    + " ORDER BY 1,2 "
                    ;

                Result = INTEXDBC.Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    //byte[] b;
                    //string value;
                    //foreach (DataRow dw in Result.Tables[TableName].Rows)
                    //{
                    //    b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["REMARK"].ToString().Trim());
                    //    value = Encoding.GetEncoding("gb2312").GetString(b);
                    //    dw["REMARK"] = value;

                    //}
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        public DataSet GetWB14TOT(DataRow dwTABEL)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "WB14R";
            string ART = dwTABEL["ART"].ToString().Trim();
            string FROMDATE = dwTABEL["FROMDATE"].ToString().Trim();
            string ENDDATE = dwTABEL["ENDDATE"].ToString().Trim();
            try
            {
                orlReadCommand = INTEXDBC.OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT TO_CHAR(A.WBEWBUCHDAT) AS DAT,TRIM(A.WBEWART)||'-'||TRIM(A.WBEWFARBE) AS ARTICLE, "
                    + " SUM(A.WBEWMTR*0.001) AS MTR,SUM((CASE WHEN A.WBEWMTR<0 THEN -1 ELSE 1 END)) AS PCE "
                    + " FROM WBBEW A "
                    + " WHERE A.WBEWWAKZ = 'R' AND A.WBEWBS IN ('21','01') "
                    + " AND A.WBEWLO = '01' AND A.WBEWLB = 'PB' "
                    + " AND A.WBEWBUCHDAT >= '" + FROMDATE + "' AND A.WBEWBUCHDAT <= '" + ENDDATE + "' "
                    + " AND TRIM(A.WBEWART)||'-'||TRIM(A.WBEWFARBE) LIKE '" + ART + "%' "
                    + " GROUP BY A.WBEWART,A.WBEWFARBE,A.WBEWBUCHDAT "
                    + " UNION ALL "
                    + " SELECT '88888888' AS DAT,TRIM(A.WBEWART)||'-'||TRIM(A.WBEWFARBE) AS ARTICLE, "
                    + " SUM(A.WBEWMTR*0.001) AS MTR,SUM((CASE WHEN A.WBEWMTR<0 THEN -1 ELSE 1 END)) AS PCE "
                    + " FROM WBBEW A "
                    + " WHERE A.WBEWWAKZ = 'R' AND A.WBEWBS IN ('21','01') "
                    + " AND A.WBEWLO = '01' AND A.WBEWLB = 'PB' "
                    + " AND A.WBEWBUCHDAT >= '" + FROMDATE + "' AND A.WBEWBUCHDAT <= '" + ENDDATE + "' "
                    + " AND TRIM(A.WBEWART)||'-'||TRIM(A.WBEWFARBE) LIKE '" + ART + "%' "
                    + " GROUP BY A.WBEWART,A.WBEWFARBE "
                    + " UNION ALL "
                    + " select '99999999' AS DAT,trim(a.abaart)||'-'||trim(a.abafarbe) AS ARTICLE, "
                    + " a.abaresmg*0.001 AS MTR,0 AS PCE from icc_abax a "
                    + " where a.abaaufkrs = 'T5' AND trim(a.abaart)||'-'||trim(a.abafarbe) LIKE '" + ART + "%' "
                    + " ORDER BY 2,1 "
                    ;

                Result = INTEXDBC.Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    string b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = dw["DAT"].ToString().Trim();
                        if (b == "88888888")
                        {
                            dw["DAT"] = "入库总和";
                        }
                        if (b == "99999999")
                        {
                            dw["DAT"] = "计划数";
                        }

                    }
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        public DataSet GetWB14OUTTOT(DataRow dwTABEL)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "WB14R";
            string ART = dwTABEL["ART"].ToString().Trim();
            string FROMDATE = dwTABEL["FROMDATE"].ToString().Trim();
            string ENDDATE = dwTABEL["ENDDATE"].ToString().Trim();
            try
            {
                orlReadCommand = INTEXDBC.OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT TO_CHAR(A.WBEWBUCHDAT) AS DAT,TRIM(A.WBEWART)||'-'||TRIM(A.WBEWFARBE) AS ARTICLE, "
                    + " SUM(A.WBEWMTR*0.001) AS MTR,SUM((CASE WHEN A.WBEWMTR<0 THEN -1 ELSE 1 END)) AS PCE "
                    + " FROM WBBEW A "
                    + " WHERE A.WBEWWAKZ = 'R' AND A.WBEWBS = '20' "
                    + " AND A.WBEWLO = '01' AND A.WBEWLB = 'PB' "
                    + " AND A.WBEWBUCHDAT >= '" + FROMDATE + "' AND A.WBEWBUCHDAT <= '" + ENDDATE + "' "
                    + " AND TRIM(A.WBEWART)||'-'||TRIM(A.WBEWFARBE) LIKE '" + ART + "%' "
                    + " GROUP BY A.WBEWART,A.WBEWFARBE,A.WBEWBUCHDAT "
                    + " UNION ALL "
                    + " SELECT '88888888' AS DAT,TRIM(A.WBEWART)||'-'||TRIM(A.WBEWFARBE) AS ARTICLE, "
                    + " SUM(A.WBEWMTR*0.001) AS MTR,SUM((CASE WHEN A.WBEWMTR<0 THEN -1 ELSE 1 END)) AS PCE "
                    + " FROM WBBEW A "
                    + " WHERE A.WBEWWAKZ = 'R' AND A.WBEWBS = '20' "
                    + " AND A.WBEWLO = '01' AND A.WBEWLB = 'PB' "
                    + " AND A.WBEWBUCHDAT >= '" + FROMDATE + "' AND A.WBEWBUCHDAT <= '" + ENDDATE + "' "
                    + " AND TRIM(A.WBEWART)||'-'||TRIM(A.WBEWFARBE) LIKE '" + ART + "%' "
                    + " GROUP BY A.WBEWART,A.WBEWFARBE "
                    + " UNION ALL "
                    + " select '99999999' AS DAT,trim(a.abaart)||'-'||trim(a.abafarbe) AS ARTICLE, "
                    + " a.abaresmg*0.001 AS MTR,0 AS PCE from icc_abax a "
                    + " where a.abaaufkrs = 'T5' AND trim(a.abaart)||'-'||trim(a.abafarbe) LIKE '" + ART + "%' "
                    + " ORDER BY 2,1 "
                    ;

                Result = INTEXDBC.Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    string b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = dw["DAT"].ToString().Trim();
                        if (b == "88888888")
                        {
                            dw["DAT"] = "出库总和";
                        }
                        if (b == "99999999")
                        {
                            dw["DAT"] = "计划数";
                        }

                    }
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        public DataSet BuildData(DataRow dwUI)
        {
            /*DataSet SResult = new DataSet();
            SETINTEXDB SReturn = new SETINTEXDB(); */
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetWB14IN(dwUI);
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet BuildDataOUT(DataRow dwUI)
        {
            /*DataSet SResult = new DataSet();
            SETINTEXDB SReturn = new SETINTEXDB(); */
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetWB14OUT(dwUI);
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet BuildDataTOT(DataRow dwUI)
        {
            /*DataSet SResult = new DataSet();
            SETINTEXDB SReturn = new SETINTEXDB(); */
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetWB14TOT(dwUI);
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet BuildDataOUTTOT(DataRow dwUI)
        {
            /*DataSet SResult = new DataSet();
            SETINTEXDB SReturn = new SETINTEXDB(); */
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetWB14OUTTOT(dwUI);
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }

    }
}